﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net.Sockets;
using System.Net;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Xml;
using System.Data;
namespace FBProductsSynchronization
{
    public class DataUtility
    {
        int i = 0;
        SqlDataReader DataReader = null;
        string VirginConnection = "user id=dinacorporate_vb;" + "password=pas5w0rd;server=122.201.105.167\\SQLSERVER;" + "database=dinacorporate_vb; " + "connection timeout=30;" + "Max Pool Size = 400";

        //Input SQL command and return a data reader Modified:24/07/2012
        public SqlDataReader returnDataread(string SqlCommand)
        {
            SqlConnection myConnection = new SqlConnection(VirginConnection);
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand(SqlCommand, myConnection);
            DataReader = myCommand.ExecuteReader();
            //myConnection.Close();
            return DataReader;
        }
        //Update order_item_delivery_fb Table for imported delivery ID
        public void UpdateFbTable(int deliveryId, int imported)
        {
            String InsertFbTable = "INSERT INTO order_item_delivery_fb_tbl (order_item_delivery_id ,order_item_delivery_fb_imported) " +
                                   "VALUES(" + deliveryId + "," + imported + ")";
            SqlConnection myConnection = new SqlConnection(VirginConnection);
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand(InsertFbTable, myConnection);
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
        //Return a list of order items(Contract Invoice)
        public string returnOrderitems(int InvoiceNumber)
        {
            string returnOrderItemsSql = "select * from order_item_delivery_tbl " +
                                         "left join order_item_tbl on order_item_delivery_tbl.order_item_id = order_item_tbl.order_item_id " +
                                          "where order_item_delivery_invoice_number=" + InvoiceNumber;
            string OrderSaleItems = "";
            int i = 1;
            try
            {
                SqlDataReader myReader = returnDataread(returnOrderItemsSql);

                while (myReader.Read())
                {
                    OrderSaleItems = OrderSaleItems +
                        "<SalesOrderItem>" +
                        "<ID>" + myReader["order_item_delivery_id"].ToString() + "</ID>" +
                        //Reminder:Need to update product number
                        "<ProductNumber>" + myReader["order_item_name"].ToString() + "</ProductNumber>" +
                        //"<Description>" + myReader["order_item_description"].ToString() + "</Description>" +
                        "<Taxable>false</Taxable>" +
                        "<Quantity>" + myReader["order_item_delivery_qty"].ToString() + "</Quantity>" +
                        "<ProductPrice>" + myReader["order_item_price"].ToString() + "</ProductPrice>" +
                        "<TotalPrice>" + myReader["order_item_price"].ToString() + "</TotalPrice>" +
                        "<UOMCode>ea</UOMCode>" +
                        "<ItemType>10</ItemType>" +
                        "<Status>40</Status>" +
                        "<QuickBooksClassName>None</QuickBooksClassName>" +
                        "<NewItemFlag>false</NewItemFlag>" +
                        "<LineNumber>" + i + "</LineNumber>" +
                        "</SalesOrderItem>";
                    try
                    {
                        UpdateFbTable(int.Parse(myReader["order_item_delivery_id"].ToString()), 1);
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message.ToString());
                    }
                    i++;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
            return OrderSaleItems;
        }
        // For VB Invoice
        public string returnOrderitems1(int deliveryDay, int monthDelivery, int yearDelivery, int deliveryType, string locationGroup)
        {
            string OrderSaleItems = "";
            int i = 1;
            string returnOrderItemsSql = "select * from order_item_delivery_tbl " +
                                        "left join order_item_delivery_type_tbl on order_item_delivery_type_tbl.order_item_delivery_id = order_item_delivery_tbl.order_item_delivery_id " +
                                        "left join order_item_tbl on order_item_delivery_tbl.order_item_id = order_item_tbl.order_item_id " +
                                        "where order_item_delivery_type= " + deliveryType + " and day(order_item_delivery_date)=" + deliveryDay +
                                        " and month(order_item_delivery_date)=" + monthDelivery + " and YEAR(order_item_delivery_date)=" + yearDelivery +
                                        " and order_item_delivery_location='" + locationGroup + "'";
            try
            {
                SqlDataReader myReader = returnDataread(returnOrderItemsSql);

                while (myReader.Read())
                {
                    if (myReader.HasRows && int.Parse(myReader["order_item_delivery_qty"].ToString()) > 0 && float.Parse(myReader["order_item_price"].ToString()) >0)
                    {
                        //Console.WriteLine(myReader["order_item_name"].ToString());
                        OrderSaleItems = OrderSaleItems +
                        "<SalesOrderItem>" +
                            //"<ID>" + myReader["order_item_delivery_id"].ToString() + "</ID>" +
                        "<ID>" + myReader["order_item_delivery_id"].ToString() + i + "</ID>" +
                            //Reminder:Need to update product number
                        "<ProductNumber>" + myReader["order_item_name"].ToString() + "</ProductNumber>" +
                            //"<ProductNumber>" + "Adelia-10 " + "</ProductNumber>" +
                            //"<Description>" + myReader["order_item_description"].ToString() + "</Description>" +
                        "<Taxable>false</Taxable>" +
                        "<Quantity>" + myReader["order_item_delivery_qty"].ToString() + "</Quantity>" +
                        "<ProductPrice>" + myReader["order_item_price"].ToString() + "</ProductPrice>" +
                        "<TotalPrice>" + myReader["order_item_price"].ToString() + "</TotalPrice>" +
                        "<UOMCode>ea</UOMCode>" +
                        "<ItemType>10</ItemType>" +
                        "<Status>10</Status>" +
                        "<QuickBooksClassName>None</QuickBooksClassName>" +
                        "<NewItemFlag>false</NewItemFlag>" +
                        "<LineNumber>" + i + "</LineNumber>" +
                        "</SalesOrderItem>";
                        try
                        {
                            UpdateFbTable(int.Parse(myReader["order_item_delivery_id"].ToString()), 1);
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.Message.ToString());
                        }
                        i++;
                    }
                    else if (float.Parse(myReader["order_item_price"].ToString()) <= 0)
                    {
                        Console.WriteLine("Price need to be positive");
                    }
                    else if (int.Parse(myReader["order_item_delivery_qty"].ToString()) <= 0)
                    {
                        Console.WriteLine("Quantity need to be positive");
                    }
                    else
                    {
                        Console.WriteLine("There is no sale order items");
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
            return OrderSaleItems;
        }
        public string FormatDate(string tmpDate)
        {
            return DateTime.Parse(tmpDate).ToString("yyyy-MM-ddTHH:mm:ss");
        }
        //For Contract Invoice
        public string SaveSO(string returnOrderSql, ConnectionObject connectionObject, string key)
        {
            SqlDataReader myReader = returnDataread(returnOrderSql);
            string SOnumber = "";
            string InvoiceDate = "";
            if (myReader.HasRows)
            {
                Console.WriteLine("### Start saving Sale Order ###");
                while (myReader.Read())
                {
                    //myReader.Read();
                    InvoiceDate = myReader["order_item_delivery_date"].ToString();
                    string CreateSaleOrderResult = connectionObject.sendCommand(Fishbowl.CreateSaleOrder(myReader, key));
                    SOnumber = myReader["order_item_delivery_invoice_number"].ToString();
                    //Console.Write(CreateSaleOrderResult);
                    //SavePick(connectionObject, key, SOnumber);
                    //SaveShip(connectionObject, key, SOnumber, InvoiceDate);
                    Console.WriteLine("Order " + SOnumber + " ");
                }
                Console.WriteLine("### Finish saving Sale Order ");
            }
            else
            {
                Console.WriteLine("### There is no sale order or They have been imported ###");
            }
            return SOnumber;
        }
        // For VB Invoice
        public string SaveSO1(string returnOrderSql, ConnectionObject connectionObject, string key)
        {
            Console.WriteLine("### Start saving VB invoices ###");
            SqlDataReader myReader = returnDataread(returnOrderSql);
            string SOnumber = "";
            string InvoiceDate = "";
            if (myReader.HasRows)
            {
                while (myReader.Read())
                {
                    //myReader.Read();
                    InvoiceDate = myReader["deliverydate"].ToString();
                    int DeliveryType = int.Parse(myReader["order_item_delivery_type"].ToString());
                    string locationGroup = myReader["order_item_delivery_location"].ToString();
                    string CreateSaleOrderResult = connectionObject.sendCommand(Fishbowl.CreateSaleOrder1(myReader, key));
                    if (CreateSaleOrderResult == "") Console.Write("empty");
                    SOnumber = ContractInvNum(InvoiceDate, DeliveryType, locationGroup);
                    //Console.Write(CreateSaleOrderResult);
                    Console.WriteLine("Order " + SOnumber);
                    //SavePick(connectionObject, key, SOnumber);
                    //SaveShip(connectionObject, key, SOnumber, InvoiceDate);
                }
            }
            else
            {
                Console.WriteLine("### There is no sale order or They have been imported ###");
            }
            Console.WriteLine("### Finish saving VB invoices ###");
            return SOnumber;
        }

        public void SavePick(ConnectionObject connectionObject, string key, string SOnumber)
        {

            try
            {
                XDocument returnGetPick = XDocument.Parse(connectionObject.sendCommand(Fishbowl.GetPick(key, SOnumber)));
                int returnStatus = 0;
                //nsole.WriteLine(returnGetPick);
                if (returnGetPick.Descendants("Pick").Count() > 0)
                {
                    foreach (XElement Pick in returnGetPick.Descendants("Pick"))
                    {
                        Pick.Element("Status").SetValue("Finished");
                        //Console.WriteLine(element.Element("Status").ToString());
                        if (Pick.Descendants("PickItem").Count() > 0)
                        {
                            foreach (XElement PickItem in Pick.Descendants("PickItem"))
                            {
                                returnStatus = int.Parse(PickItem.Element("Status").Value.ToString());
                                if (returnStatus == 5)
                                    Console.WriteLine("!!" + PickItem.Element("Part").Element("Num").Value + " is out of stock.Please update stock!!");
                                else
                                    PickItem.Element("Status").SetValue(40);
                                //Console.WriteLine(status.Element("Status").ToString());
                            }
                            string returnPick = connectionObject.sendCommand(Fishbowl.SavePick(key, Pick.ToString()));
                            //Console.WriteLine(returnPick);
                            //Console.WriteLine("*** Finish packaging Sale Order " + SOnumber + "***");
                        }
                        else
                        {
                            //Console.WriteLine("### Pick item not found ###");
                        }

                    }
                }
                else
                {
                    //Console.WriteLine("### Pick Not found!.Please check sale order number ###");
                }
            }
            catch (Exception e)
            {
                //Console.WriteLine(e.Message.ToString());
            }
        }
        public void SaveShip(ConnectionObject connectionObject, string key, string SOnumber, string deliveryDate)
        {
            if (SOnumber != "")
            {
                string returnShipped = connectionObject.sendCommand(Fishbowl.SaveShip(key, SOnumber, deliveryDate));
                //Console.WriteLine(returnShipped);
                //Console.WriteLine("~~~ Sale Order " + SOnumber + " has been shipped ~~~");
            }
        }
        //Format InvoiceNumber for VB Invoice, ex: VBYYYYMMTT-DD-LLL
        public string ContractInvNum(string tmpDate, int deliveryType, string locationGroup)
        {
            if (locationGroup == "SYDNEY") locationGroup = "SYD";
            if (locationGroup == "MELBOURNE") locationGroup = "MEL";
            if (locationGroup == "BRISBANE") locationGroup = "BNE";
            return DateTime.Parse(tmpDate).ToString("VByyyyMM" + deliveryType.ToString("00") + "-dd-").ToString() + locationGroup;
        }
        //Return LocationGroup, following the format of Fishbowl SYDNEY -> Sydney
        public string LocationGroup(string locationGroup)
        {
            if (locationGroup == "SYDNEY") return locationGroup = "Mascot";
            if (locationGroup == "MELBOURNE") return locationGroup = "Melbourne";
            if (locationGroup == "BRISBANE") return locationGroup = "Brisbane";
            else
                return locationGroup;
        }
        //Return product number, quantity, status(processed,back ordered, shipped, packed)
        public List<OrderStatus> StoreOrderStatus(ConnectionObject connectionObject, string key, string SOnumber)
        {
            List<OrderStatus> OrderStatusList = new List<OrderStatus>();
            try
            {
                XDocument returnGetPick = XDocument.Parse(connectionObject.sendCommand(Fishbowl.GetPick(key, SOnumber)));
                string productStatus = "";
                string msg = "";
                int returnStatus = 0;
                int i = 1;
                //nsole.WriteLine(returnGetPick);
                if (returnGetPick.Descendants("Pick").Count() > 0)
                {
                    foreach (XElement Pick in returnGetPick.Descendants("Pick"))
                    {
                        Pick.Element("Status").SetValue("Finished");
                        //Console.WriteLine(element.Element("Status").ToString());
                        if (Pick.Descendants("PickItem").Count() > 0)
                        {
                            foreach (XElement PickItem in Pick.Descendants("PickItem"))
                            {
                                //Console.WriteLine(PickItem.Element("Part").Element("Num"));
                                //Console.WriteLine(PickItem.Element("OrderItemID"));
                                //Console.WriteLine(PickItem.Element("Quantity"));
                                //Console.WriteLine(PickItem.Element("Status"));

                                string orderItemId = PickItem.Element("OrderItemID").Value.ToString();
                                //Get status of the items ex:40:shipped/packed,20:processed,6:out of stock
                                returnStatus = int.Parse(PickItem.Element("Status").Value.ToString());
                                OrderStatus orderStatus = new OrderStatus();
                                if (returnStatus == 40)
                                {
                                    msg = returnMsg(connectionObject, key, SOnumber, orderItemId);
                                }
                                //Return status msg by using status code. Fucntion:StatusMsg
                                productStatus = StatusMsg(returnStatus);
                                //Add each data to object
                                orderStatus.ProductID = i;
                                orderStatus.ProductNumber = PickItem.Element("Part").Element("Num").Value.ToString();
                                orderStatus.ProductStatus = productStatus;
                                orderStatus.StatusMessage = msg;
                                orderStatus.Quantity = int.Parse(PickItem.Element("Quantity").Value.ToString());
                                //Add each object to OrderStatusList
                                OrderStatusList.Add(orderStatus);
                                i++;
                            }
                        }
                        else
                        {
                            Console.WriteLine("### Pick item not found ###");
                        }
                    }
                }
                else
                {
                    Console.WriteLine("### Pick Not found!.Please check sale order number ###");
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
            return OrderStatusList;
        }
        //Return a ship list of a sale order
        public string GetShipList(ConnectionObject connectionObject, string key, string SOnumber)
        {
            string returnShipList = connectionObject.sendCommand(Fishbowl.GetShipList(key, SOnumber));
            //Console.Write(returnShipList);
            return returnShipList;
        }
        //Return a shipment by a ship ID from ship list
        public string GetShipMent(ConnectionObject connectionObject, string key, int shipID)
        {
            string returnShipMent = connectionObject.sendCommand(Fishbowl.GetShipment(key, shipID));
            //Console.Write(returnShipMent);
            return returnShipMent;
        }
        //Return Status msg by a status number
        public string StatusMsg(int status)
        {
            string statusMsg = "";
            if (status == 40) statusMsg = "Shipped";
            else if (status == 20) statusMsg = "Processed";
            else if (status == 6) statusMsg = "On Hold - Back Ordered";
            else if (status == 5) statusMsg = "Out Of Stock";
            return statusMsg;
        }
        //Return tracking number if exists
        public string checkTrackingNum(string TrackingNumber)
        {
            if (TrackingNumber != "<TrackingNum />") { return TrackingNumber; }
            else { return "no tracking number"; }
        }
        //Return msg(mostly tracking number), only return track number if shipped
        public string returnMsg(ConnectionObject connectionObject, string key, string SOnumber, string orderItemId)
        {
            string msg = "";
            string returnShipList = GetShipList(connectionObject, key, SOnumber);
            XDocument ShipList = XDocument.Parse(returnShipList);
            foreach (XElement element in ShipList.Descendants("ShipSearchItem"))
            {
                string ShipmentResult = GetShipMent(connectionObject, key, int.Parse(element.Element("ShipID").Value.ToString()));
                XDocument ShippedItemList = XDocument.Parse(ShipmentResult);
                foreach (XElement carton in ShippedItemList.Descendants("Carton"))
                {
                    foreach (XElement ShippedItem in ShippedItemList.Descendants("ShippingItem"))
                    {
                        string orderItemIdShip = ShippedItem.Element("OrderItemID").Value.ToString();
                        if (orderItemId == orderItemIdShip && element.Element("Status").Value.ToString() == "Shipped")
                        {
                            string TrackingNumber = carton.Element("TrackingNum").Value.ToString();
                            msg = checkTrackingNum(TrackingNumber);
                        }
                        else
                        {
                            msg = "no tracking number";
                        }
                    }
                }
            }
            return msg;
        }
        //Return OrderStatus Detail
        public void returnOrderStatus(List<OrderStatus> OrderStatusList)
        {
            foreach (OrderStatus orderStatus in OrderStatusList)
            {
                Console.WriteLine(orderStatus.ProductID);
                Console.WriteLine(orderStatus.ProductNumber);
                Console.WriteLine(orderStatus.ProductStatus);
                Console.WriteLine(orderStatus.StatusMessage);
                Console.WriteLine(orderStatus.Quantity + "\r");
                i++;
                Console.WriteLine(i + " times");
            }
        }
        //Gett all products from Fishbowl and then import them to Database (FB_Item_Tbl)
        public void ExportProducts(ConnectionObject connectionObject, string key, string exportType, string stockName)
        {
            Console.WriteLine("Start sync products from fishbowl "+ DateTime.Now+" .........");
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            try
            {
                string returnexport = connectionObject.sendCommand(Fishbowl.Export(key, exportType));
                string ExportInventory = connectionObject.sendCommand(Fishbowl.Export(key, "ExportInventoryQuantities"));
                XDocument rowlist = XDocument.Parse(returnexport);
                //Count if Product is added to database. Return Zero 0 if empty
                int j = 0;

                foreach (XElement element in rowlist.Descendants("Row"))
                {
                    List<string> list = SeperateWords(element.Value.ToString());

                    string productNum = list[0].Substring(1, list[0].Length - 2);
                    string productCategory = list[1].Substring(1, list[1].Length - 2);
                    // Some Products contain quote so we have to replace ' by '' otherwise it will cause problem in SQL command below
                    string NextPurchase = NextPurchaseDate(connectionObject, key, productNum);
                    if (existProductNum(productNum.Replace("'", "''")) == false && productNum != "ProductNumber")
                    {
                        string returnProduct = connectionObject.sendCommand(Fishbowl.ProductRQ(key, productNum));
                        XDocument Xproduct = XDocument.Parse(returnProduct);
                        foreach (XElement xelement in Xproduct.Descendants("Product").ToList())
                        {
                            string upc = xelement.Element("UPC").Value.ToString();
                            //Remove part element because we don't need it
                            xelement.Element("Part").Remove();
                            // We have to know the name of the company ex: Product:Virgin
                            if (productCategory == stockName)
                            {
                                Console.WriteLine(productNum);
                                virginConnection.Open();
                                SqlCommand command = new SqlCommand("SP_FB_Products_Insert", virginConnection);
                                command.CommandType = CommandType.StoredProcedure;
                                command.Parameters.Add(new SqlParameter("@Product_PartID", SqlDbType.Int, 0, "Product_PartID"));
                                command.Parameters.Add(new SqlParameter("@Product_Upc", SqlDbType.NVarChar, 150, "Product_Upc"));
                                command.Parameters.Add(new SqlParameter("@Product_Num", SqlDbType.NVarChar, 50, "Product_Num"));
                                command.Parameters.Add(new SqlParameter("@Product_Desc", SqlDbType.NVarChar, 50, "Product_Desc"));
                                command.Parameters.Add(new SqlParameter("@Product_Price", SqlDbType.Float, 0, "Product_Price"));
                                command.Parameters.Add(new SqlParameter("@Product_UOMID", SqlDbType.Int, 0, "Product_UOMID"));
                                command.Parameters.Add(new SqlParameter("@Product_Date", SqlDbType.DateTime, 0, "Product_Date"));
                                command.Parameters.Add(new SqlParameter("@Product_NextPurchase", SqlDbType.NVarChar, 50, "Product_NextPurchase"));

                                command.Parameters[0].Value = int.Parse(xelement.Element("PartID").Value.ToString());
                                command.Parameters[1].Value = upc;
                                command.Parameters[2].Value = xelement.Element("Num").Value.ToString();
                                command.Parameters[3].Value = xelement.Element("Description").Value.ToString();
                                command.Parameters[4].Value = float.Parse(xelement.Element("Price").Value.ToString());
                                command.Parameters[5].Value = int.Parse(xelement.Element("UOM").Element("UOMID").Value.ToString());
                                //Datetime from Fishbowl always includes T so we have to remove T
                                command.Parameters[6].Value = DateTime.Parse(xelement.Element("DateModified").Value.ToString().Replace("T", " "));
                                command.Parameters[7].Value = NextPurchase;

                                int y = command.ExecuteNonQuery();
                                //Add stock here
                                AddStock(connectionObject, key,ExportInventory, productNum);
                                virginConnection.Close();
                                j = j + 1;
                            }
                        }
                        //Clear the list for another product
                        list.Clear();
                    }
                    else if (existProductNum(productNum.Replace("'", "''")) == true && productNum != "ProductNumber")
                    {
                        AddStock(connectionObject, key, ExportInventory, productNum);
                    }
                }
                Console.WriteLine(j + " Products have been sync");
                Console.WriteLine("Finish sync products from fishbowl to database at "+ DateTime.Now);
                virginConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
        }
        //Reading each line and then get the position of the value and then store the positions to a list
        public List<int> positionProduct(XElement element)
        {
            int i = 0;
            List<int> list = new List<int>();
            foreach (char c in element.Value.ToString())
            {
                //If character equals to a quote then get the position.THere should be 4 quotes, each two for a data
                if (c == '\"')
                {
                    list.Add(i);
                }
                i++;
            }
            return list;
        }
        //Check if Product Number exist in the database, if exis return true otherwise return false
        public bool existProductNum(string productNum)
        {
            bool flag = false;
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            try
            {
                virginConnection.Open();
                string sql = "select * from FB_item_tbl where Product_Num = '" + productNum + "'";
                SqlCommand command = new SqlCommand(sql, virginConnection);
                object result = command.ExecuteScalar();
                if (result != null)
                    flag = true;
                else
                    flag = false;
                virginConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return flag;
        }
        public bool existStock(string productNum,string location, string quantity)
        {
            bool flag = false;
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            try
            {
                virginConnection.Open();
                string sql = "select * from Stock_FB_tbl where Part_number = '" + productNum + "' and Stock_location='" + location + "'";
                SqlCommand command = new SqlCommand(sql, virginConnection);
                object result = command.ExecuteScalar();
                if (result != null)
                    flag = true;
                else
                    flag = false;
                virginConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return flag;
        }
      
        //Add stock from FB to database(Stock_FB_tbl)
        public void AddStock(ConnectionObject connectionObject, string key, string stock, string ProductNum)
        {
            try
            {
                //Console.WriteLine("Start sync inventory from FB to database");
                int j = 0;
                SqlConnection virginConnection = new SqlConnection(VirginConnection);
                string returnStockList = stock;
                //Console.WriteLine(returnStockList);
                
                XDocument rowlist = XDocument.Parse(returnStockList);
                foreach (XElement element in rowlist.Descendants("Row").ToList())
                {
                    List<String> listWords = SeperateWords(element.Value.ToString());
                    //Console.WriteLine(element.Value.ToString());
                    //Console.WriteLine(list[0] + " " + list[1] + list[2] + " " + list[3]);
                    string product = listWords[0].Substring(1, listWords[0].Length - 2);
                    string stockLocation = listWords[2].Substring(1, listWords[2].Length - 2);
                    string stockQuantity = listWords[3].Substring(1, listWords[3].Length - 2);
                    //Console.WriteLine(product + stockLocation + stockQuantity);
                    if (product == ProductNum)
                    {
                        if (existStock(product.Replace("'", "''"), stockLocation, stockQuantity) == true && product != "PartNumber")
                        {
                            virginConnection.Open();
                            SqlCommand command1 = new SqlCommand("SP_update_Stock_FB", virginConnection);
                            command1.CommandType = CommandType.StoredProcedure;
                            command1.Parameters.Add(new SqlParameter("@Part_number", SqlDbType.NVarChar, 50, "Part_number"));
                            command1.Parameters.Add(new SqlParameter("@Stock_location", SqlDbType.NVarChar, 150, "Stock_location"));
                            command1.Parameters.Add(new SqlParameter("@Stock_Quantity", SqlDbType.Int, 0, "Stock_Quantity"));
                            command1.Parameters[0].Value = product;
                            command1.Parameters[1].Value = stockLocation;
                            command1.Parameters[2].Value = int.Parse(stockQuantity.ToString());
                            command1.ExecuteNonQuery();
                            Console.WriteLine("update stock for " + product);
                            virginConnection.Close();
                            j++;
                        }
                        else if (existStock(product.Replace("'", "''"), stockLocation, stockQuantity) == false && product != "PartNumber")
                        {
                            virginConnection.Open();
                            SqlCommand command1 = new SqlCommand("SP_FB_Stock_Insert", virginConnection);
                            command1.CommandType = CommandType.StoredProcedure;
                            command1.Parameters.Add(new SqlParameter("@Part_number", SqlDbType.NVarChar, 50, "Part_number"));
                            command1.Parameters.Add(new SqlParameter("@Stock_location", SqlDbType.NVarChar, 150, "Stock_location"));
                            command1.Parameters.Add(new SqlParameter("@Stock_Quantity", SqlDbType.Int, 0, "Stock_Quantity"));
                            command1.Parameters[0].Value = product;
                            command1.Parameters[1].Value = stockLocation;
                            command1.Parameters[2].Value = int.Parse(stockQuantity.ToString());
                            command1.ExecuteNonQuery();
                            Console.WriteLine("add stock for " + product);
                            virginConnection.Close();
                            j++;
                        }
                    }
                    listWords.Clear();
                }
                //Console.WriteLine(j + " products have been sync");
                //Console.WriteLine("Finish sync inventory from FB to database");
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
        }
        public List<String> SeperateWords(string words)
        {
            List<String> listWords = new List<String>();
            string[] tmpwords = words.Split(',');
            foreach (string word in tmpwords)
            {
                listWords.Add(word);
            }
            return listWords;
        }
        //Return next purchase date, it returns a string because FB return date in 07/08/12
        public string NextPurchaseDate(ConnectionObject connectionObject, string key, string product)
        {
            string returnReceivingList = connectionObject.sendCommand(Fishbowl.Export(key, "ExportReceivingData"));
            string dateSchedule ="";
            XDocument rowlist = XDocument.Parse(returnReceivingList);
            foreach (XElement element in rowlist.Descendants("Row"))
            {
                List<String> list = SeperateWords(element.Value.ToString());
                string productNum = list[3].Substring(1, list[3].Length - 2);
                if (productNum == product)
                    dateSchedule = list[8].Substring(1, list[8].Length - 2);
                
            }
            return dateSchedule;
        }
    }
}

